﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.IO;

class ADO_Excercises
{
    public static void Ex5()
    {
        SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
       "Database=NorthWind2; Integrated Security=true");
        dbCon.Open();
        using (dbCon)
        {
            SqlCommand cmdAllCategories = new SqlCommand(
                 "SELECT CategoryName, ProductName " +
                "FROM Categories c JOIN  Products p ON c.CategoryID=p.CategoryID " +
                "GROUP BY CategoryName, ProductName", dbCon);

            SqlDataReader reader = cmdAllCategories.ExecuteReader();
            using (reader)
            {
                while (reader.Read())
                {
                    string category = (string)reader["CategoryName"];
                    string product = (string)reader["ProductName"];
                    Console.WriteLine("{0} {1}", category, product);
                }
            }
        }
    }

    public static bool Ex6(string productName, int supplierID, int categoryID, string quantityPerUnit,
        decimal unitPrice, short unitsInStock, short unitsInOrder, short reorderLevel, bool discontinued)
    {
        try
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
           "Database=NorthWind2; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmdInsertProduct = new SqlCommand(
               "INSERT INTO Products(ProductName,  SupplierID,  CategoryID,  QuantityPerUnit," +
             " UnitPrice,  UnitsInStock,  UnitsOnOrder,  ReorderLevel,  Discontinued) " +
               "VALUES (@productName,  @supplierID,  @categoryID,  @quantityPerUnit," +
             " @unitPrice,  @unitsInStock,  @unitsInOrder,  @reorderLevel,  @discontinued) ", dbCon);
                cmdInsertProduct.Parameters.Add(new SqlParameter("@productName", productName));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@supplierID", supplierID));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@categoryID", categoryID));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@quantityPerUnit", quantityPerUnit));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@unitPrice", unitPrice));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@unitsInStock", unitsInStock));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@unitsInOrder", unitsInOrder));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@reorderLevel", reorderLevel));
                cmdInsertProduct.Parameters.Add(new SqlParameter("@discontinued", discontinued));

                cmdInsertProduct.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
        return true;
    }

    public static bool Ex9(string cardNumber, string PIN, decimal withdrawSum)
    { 
        if(withdrawSum< 0)
        {
            Console.WriteLine("Cannot Withdraw negative sum");
            return false;
        }
        SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
           "Database=ATM; Integrated Security=true");
        dbCon.Open();
        using (dbCon)
        {
            SqlTransaction trans =
                dbCon.BeginTransaction(IsolationLevel.Serializable);
            Console.WriteLine("Transaction started.");

            try
            {

                SqlCommand cmdGetAccount = new SqlCommand(
                 "SELECT CardCash FROM CardAccounts WHERE CardNumber = @cardNumber AND CardPin = @PIN", dbCon,trans);
                cmdGetAccount.Parameters.AddWithValue("@cardNumber",cardNumber);
                cmdGetAccount.Parameters.AddWithValue("@PIN", PIN);
            
                decimal? money = (decimal?)cmdGetAccount.ExecuteScalar();
                if(money==null)
                {
                    Console.WriteLine("Wrong account ot PIN");
                    return false;
                }
                
                if(money<withdrawSum)
                {
                    Console.WriteLine("Not enough funds");
                    return false;
                }

                SqlCommand cmdWithdrawMoney = new SqlCommand("UPDATE CardAccounts SET CardCash = CardCash - @withdraw"
                    +" WHERE CardNumber = @cardNumber AND CardPin = @PIN",dbCon,trans);
                cmdWithdrawMoney.Parameters.AddWithValue("@withdraw", withdrawSum);
                cmdWithdrawMoney.Parameters.AddWithValue("@cardNumber", cardNumber);
                cmdWithdrawMoney.Parameters.AddWithValue("@PIN", PIN);


                SqlCommand cmdAddWithdrawLog = new SqlCommand("INSERT INTO AccountTransactions (CardNumber , TransactionDate, Ammount)" +
                    " VALUES(@cardNumber, @date, @ammount)", dbCon, trans);
                cmdAddWithdrawLog.Parameters.AddWithValue("@ammount", -withdrawSum);
                cmdAddWithdrawLog.Parameters.AddWithValue("@cardNumber", cardNumber);
                cmdAddWithdrawLog.Parameters.AddWithValue("@date", DateTime.Now);

                cmdAddWithdrawLog.ExecuteNonQuery();
                cmdWithdrawMoney.ExecuteNonQuery();
                

                Console.WriteLine("Updated a new record.");
                trans.Commit();
                Console.WriteLine("Transaction comitted.");
            }
            catch (SqlException e)
            {
                Console.WriteLine("Exception occured: {0}", e.Message);
                trans.Rollback();
                Console.WriteLine("Transaction cancelled.");
                return false;
            }
        }
        return true;
    }

    public static void Ex11(string cardNumber, string PIN)
    {
         SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
           "Database=ATM; Integrated Security=true");
        dbCon.Open();
        using (dbCon)
        {
            SqlCommand cmdGetAccount = new SqlCommand(
                 "SELECT COUNT(*) FROM CardAccounts WHERE CardNumber = @cardNumber AND CardPin = @PIN", dbCon);
            cmdGetAccount.Parameters.AddWithValue("@cardNumber", cardNumber);
            cmdGetAccount.Parameters.AddWithValue("@PIN", PIN);

            int exists = (int)cmdGetAccount.ExecuteScalar();
            if (exists == 0)
            {
                Console.WriteLine("Wrong accountID or/and PIN");
                return;
            }

            SqlCommand cmdGetLog = new SqlCommand(
                "SELECT AccountTransactionID, TransactionDate, Ammount FROM AccountTransactions WHERE CardNumber = @cardNumber ORDER BY TransactionDate", dbCon);
            cmdGetLog.Parameters.AddWithValue("@cardNumber", cardNumber);
            cmdGetLog.Parameters.AddWithValue("@PIN", PIN);

            using (SqlDataReader reader = cmdGetLog.ExecuteReader())
            {
                Console.WriteLine("Transactions for {0}: ",cardNumber);
                while (reader.Read())
                {
                    int id = (int)reader["AccountTransactionID"];
                    DateTime date = (DateTime)reader["TransactionDate"];
                    decimal ammount = (decimal)reader["Ammount"];
                    Console.WriteLine("{0}: {1}$ -- {2}", id, ammount, date);
                }
            }
        }
    }

    public static void Ex12_ReadVideo(int videoID, string outputFileName)
    {
        try
        {
            using (SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
               "Database=ATM; Integrated Security=true"))
            {
                using (SqlCommand cmdReadVideo = dbCon.CreateCommand())
                {
                    dbCon.Open();
                    cmdReadVideo.CommandText = "SELECT VideoData FROM Videos WHERE VideoID = @videoID";
                    cmdReadVideo.Parameters.AddWithValue("@videoID", videoID);
                    using (FileStream fileWriter = new FileStream(outputFileName, FileMode.Create, FileAccess.Write))
                    {
                        using (SqlDataReader reader = cmdReadVideo.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                byte[] buffer = new byte[1024 * 1024];
                                long bytesRead = 0;
                                long dataIndex = 0;
                                while ((bytesRead = reader.GetBytes(0, dataIndex, buffer, 0, buffer.Length)) > 0)
                                {
                                    fileWriter.Write(buffer, 0, (int)bytesRead);
                                    dataIndex += bytesRead;
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            return;
        }
    }

    public static void Ex12_WriteVideo(string title, string descriptions, string inputFileName)
    {
        try
        {
            using (SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
               "Database=ATM; Integrated Security=true"))
            {
                using (SqlCommand cmdReadVideo = dbCon.CreateCommand())
                {
                    dbCon.Open();
                    cmdReadVideo.CommandText = "SELECT VideoData FROM Videos WHERE VideoID = @videoID";
                    cmdReadVideo.Parameters.AddWithValue("@videoID", videoID);
                    using (FileStream fileWriter = new FileStream(outputFileName, FileMode.Create, FileAccess.Write))
                    {
                        using (SqlDataReader reader = cmdReadVideo.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                byte[] buffer = new byte[8 * 1024];
                                long bytesRead = 0;
                                long dataIndex = 0;
                                while ((bytesRead = reader.GetBytes(0, dataIndex, buffer, 0, buffer.Length)) > 0)
                                {
                                    fileWriter.Write(buffer, 0, (int)bytesRead);
                                    dataIndex += bytesRead;
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            return;
        }
    }

    static void Main(string[] args)
    {
        //Ex5()

        //Console.WriteLine(Ex6("Toaletna", 1, 1, "2", 25.5m, 15, 15, 15, false));

        //Ex9("019240", "0941", -24m);
        //Ex9("019240", "0941", 26m);
        //Ex9("1234", "1234", 200m);

        //Ex11("1238", "1234");
        //Ex11("1234", "1234");

    }
}

